package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Level;
import com.xxxy.jsq.model.Page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class LevelDao implements BaseDao<Level> {
    private C3P0Utils db = null;

    public LevelDao() {
        this.db = new C3P0Utils();
    }

    @Override
    public String add(Level model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("insert into pa_level(level_name, level_order, level_flag) ");
        sqlStr.append("values(?,?,?) ");

        conn = db.getConnection();
        boolean flag = isSame(model, true);
        if (!flag) {
            int num = 0;
            try {
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setString(1, model.getLevelName());
                pstm.setInt(2, model.getLevelOrder());
                pstm.setString(3, model.getLevelFlag());

                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (num > 0) {
                return "Ok";
            } else {
                return "Err";
            }
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Level model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        boolean flag = isSame(model, false);
        if (flag) {
            return "Same";
        }
        sqlStr.append("update pa_level set ");
        if (model.getLevelName() != null && !model.getLevelName().equals("")) {
            sqlStr.append(" level_name='" + model.getLevelName() + "'");
        }
        if (model.getLevelOrder() != null) {
            sqlStr.append(" ,level_order=" + model.getLevelOrder());
        }
        if (model.getLevelFlag() != null && !model.getLevelFlag().equals("")) {
            sqlStr.append(" ,level_flag='" + model.getLevelFlag() + "'");
        }

        sqlStr.append(" where level_id=?");

        int num = 0;
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getLevelId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }

    @Override
    public String delete(Level model) {
        return "";
    }


    @Override
    public String deleteById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("delete from pa_level where level_id=? ");

        conn = db.getConnection();
        int num = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        if (num > 0) {
            return "Ok";
        } else {
            return "Err";
        }
    }



    @Override
    public List<Level> getAllData(Level model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Level> list = new ArrayList<>();

        sqlStr.append("SELECT * FROM pa_level ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getLevelName() != null && !model.getLevelName().equals("")) {
            sqlStr.append("AND level_name='" + model.getLevelName() + "' ");
        }
        if (model.getLevelOrder() != null) {
            sqlStr.append("AND level_order=" + model.getLevelOrder() + " ");
        }
        if (model.getLevelFlag() != null && !model.getLevelFlag().equals("")) {
            sqlStr.append("AND level_flag='" + model.getLevelFlag() + "' ");
        }
        sqlStr.append("ORDER BY level_stmp ASC, level_id ASC, level_name ASC");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Level lvl = new Level();
                lvl.setLevelId(rs.getLong("level_id"));
                lvl.setLevelName(rs.getString("level_name"));
                lvl.setLevelOrder(rs.getInt("level_order"));
                lvl.setLevelFlag(rs.getString("level_flag"));

                list.add(lvl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Level findById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT * FROM pa_level ");
        sqlStr.append("WHERE 1=1 AND level_id=? ");

        conn = db.getConnection();
        Level lvl = new Level();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();
            while (rs.next()) {
                lvl.setLevelId(rs.getLong("level_id"));
                lvl.setLevelName(rs.getString("level_name"));
                lvl.setLevelOrder(rs.getInt("level_order"));
                lvl.setLevelFlag(rs.getString("level_flag"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lvl;
    }


    @Override
    public Level find(Level model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT * FROM pa_level ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getLevelName() != null && !model.getLevelName().equals("")) {
            sqlStr.append("AND level_name='" + model.getLevelName() + "' ");
        }
        if (model.getLevelOrder() != null) {
            sqlStr.append("AND level_order=" + model.getLevelOrder() + " ");
        }
        if (model.getLevelFlag() != null && !model.getLevelFlag().equals("")) {
            sqlStr.append("AND level_flag='" + model.getLevelFlag() + "' ");
        }

        conn = db.getConnection();
        Level lvl = new Level();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                lvl.setLevelId(rs.getLong("level_id"));
                lvl.setLevelName(rs.getString("level_name"));
                lvl.setLevelOrder(rs.getInt("level_order"));
                lvl.setLevelFlag(rs.getString("level_flag"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lvl;
    }

    @Override
    public boolean isSame(Level model, boolean sflag) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT * FROM pa_level WHERE level_name=? ");
        if (!sflag) {
            sqlStr.append("AND level_id!='" + model.getLevelId() + "' ");
        }

        conn = db.getConnection();
        boolean flag = false;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getLevelName());
            rs = pstm.executeQuery();
            if (rs.next()) {
                flag = true;
            } else {
                flag = false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public List<Level> getAllDataByPage(Level model, Page page) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Level> list = new ArrayList<>();

        sqlStr.append("SELECT * FROM pa_level ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getLevelName() != null && !model.getLevelName().equals("")) {
            sqlStr.append("AND level_name LIKE '%" + model.getLevelName() + "%' ");
        }
        if (model.getLevelOrder() != null) {
            sqlStr.append("AND level_order=" + model.getLevelOrder() + " ");
        }
        if (model.getLevelFlag() != null && !model.getLevelFlag().equals("")) {
            sqlStr.append("AND level_flag='" + model.getLevelFlag() + "' ");
        }
        sqlStr.append("ORDER BY level_stmp ASC, level_id ASC, level_name ASC ");
        sqlStr.append("LIMIT ?,? ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setInt(1, (page.getCurrentPage() - 1) * page.getCount());
            pstm.setInt(2, page.getCount());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Level lvl = new Level();
                lvl.setLevelId(rs.getLong("level_id"));
                lvl.setLevelName(rs.getString("level_name"));
                lvl.setLevelOrder(rs.getInt("level_order"));
                lvl.setLevelFlag(rs.getString("level_flag"));

                list.add(lvl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(Level model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT COUNT(*) FROM pa_level ");
        sqlStr.append("WHERE 1=1 ");
        if (model.getLevelName() != null && !model.getLevelName().equals("")) {
            sqlStr.append("AND level_name='" + model.getLevelName() + "' ");
        }
        if (model.getLevelOrder() != null) {
            sqlStr.append("AND level_order=" + model.getLevelOrder() + " ");
        }
        if (model.getLevelFlag() != null && !model.getLevelFlag().equals("")) {
            sqlStr.append("AND level_flag='" + model.getLevelFlag() + "' ");
        }

        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public List<Level> getAllky() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Level> list = new ArrayList<>();

        sqlStr.append("SELECT * FROM pa_level WHERE level_flag='0' "); // 0启用，1禁用
        sqlStr.append("ORDER BY level_name ASC ");

        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Level lvl = new Level();
                lvl.setLevelId(rs.getLong("level_id"));
                lvl.setLevelName(rs.getString("level_name"));
                list.add(lvl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return list;
    }
}
